﻿using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Windows.Forms;
using ToolUtil;
using zyconfig;
using zymarc;
using zyui;

namespace zymain
{
    public partial class FrmCgMatching : Form
    {
        private string filePath = string.Empty;
        public BindingSource BookData;
        public BindingSource CgData;
        public BindingSource BzyData;
        private DataTable tabTemp = new DataTable();
        private DataTable dt = new DataTable();
        private bool flag=false;
        private List<int> DbKayList;
        private int _bzkjm;
        private int uid;
        private string marc = string.Empty, bzmarc = string.Empty , zjm = string.Empty, kjm = string.Empty;
        public FrmCgMatching()
        {
            InitializeComponent();
        }

        private void FrmCgMatching_Load(object sender, EventArgs e)
        {
            string formName = "FrmTool";
            Form fr = System.Windows.Forms.Application.OpenForms[formName];
            if (fr != null)
            {
                FrmTool f1 = (FrmTool)fr;
                //取到Form1的值是1
                this.uid = Convert.ToInt32(f1.UserID);
            }
            this.bzBox.SelectedIndex = 0;
            this.AddBzDb();
        }
        private void AddBzDb()
        {
            this.DbKayList = new List<int>();
            using (DbOperHandler _open = new Common().Doh())
            {
                if (_open.dbType.ToString().Equals("ORACLE") || _open.odbType.ToString().Equals("ORACLE"))
                    flag = true;
                else
                    flag = false;
                _open.Reset();
                _open.SqlCmd = "select * from 书目数据库定义 where 文献类型='MARC数据' order by 库键码";
                DataTable dt = _open.GetDataTable();
                foreach(DataRow dRow in dt.Rows)
                {
                    this.skKay.Items.Add(dRow["书目库名"].ToString().Trim());
                    this.DbKayList.Add(Convert.ToInt32("0"+dRow["库键码"].ToString().Trim()));
                }
                if (dt.Rows.Count > 0)
                    this.skKay.SelectedIndex = 0;
            }
        }
        private void excelBtn_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Excel文件";
            ofd.FileName = "";
            //ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统文件夹，可以使用System.Environment类的静态方法GetFolderPath()。该方法接受一个Environment.SpecialFolder枚举，其中可以定义要返回路径的哪个系统目录
            ofd.Filter = "Excel文件| *.xlsx";
            ofd.RestoreDirectory = true;//记录上次打开的目录
            ofd.Multiselect = false; //不允许选中多个文件
            ofd.ValidateNames = true;     //文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名
            ofd.CheckFileExists = true;  //验证路径有效性
            ofd.CheckPathExists = true; //验证文件有效性
            string strName = string.Empty;
            if (string.IsNullOrEmpty(filePath))
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    strName = ofd.FileName;
                    filePath = strName;
                }
            }
            else
            {
                if (MessageBox.Show("确认要清除当前文件，重新加载吗？", "询问", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
                {
                    filePath = "";
                    listBox1.Items.Clear();
                    if (ofd.ShowDialog() == DialogResult.OK)
                    {
                        strName = ofd.FileName;
                        filePath = strName;
                    }
                }
            }
            if (strName == "")
            {
                MessageBox.Show("没有选择Excel文件！无法进行数据导入");
                return;
            }
            ExcelListBox(strName);
            if (this.listBox1.Items.Count > 0)
            {
                this.listBox1.SelectedIndex = 0;
            }
        }
        /// <summary>
        /// 遍历excel工作表数据，返回DataTable
        /// </summary>
        /// <param name="excelpath"></param>
        /// <param name="listBox"></param>
        /// <returns></returns>
        private DataTable ReadFromExcel(string excelpath, string listBox)
        {
            string sExt = System.IO.Path.GetExtension(excelpath);
            if (sExt == ".xlsx" || sExt == ".xls")
            {
                //Stopwatch watch = new Stopwatch();
                //watch.Start();
                DataTable excelTable = OpenXMLHelper.ExcelWorksheetToDataTable(excelpath, listBox);
                //string time1 = watch.Elapsed.ToString();
                //MessageBox.Show("耗时：" + time1);
                return excelTable;
            }
            else
            {
                MessageBox.Show("文件格式有误");
                return null;
            }
        }
        /// <summary>
        /// 读取文件工作表
        /// </summary>
        /// <param name="excelpath"></param>
        private void ExcelListBox(string excelpath)
        {
            //Stopwatch watch = new Stopwatch();
            //watch.Start();
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(excelpath, false))
            {
                WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                IEnumerable<Sheet> sheets = workbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                int m = 0;
                foreach (OpenXmlElement s in sheets)
                {
                    foreach (OpenXmlAttribute attr in s.GetAttributes())
                    {
                        if (m % 3 == 0)//因为attr.Value中包含工作表名,工作表序号和ID号
                        {
                            this.listBox1.Items.Add(attr.Value);//把工作表名装到List中
                        }
                        m++;
                    }
                }
            }
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            Stopwatch watch = new Stopwatch();
            watch.Start();
            FrmWait wait = new FrmWait();
            wait.Show();
            wait.label4.Text = "";
            Application.DoEvents();
            DataTable table = new DataTable();
            table = ReadFromExcel(filePath, listBox1.SelectedItem.ToString());
            if (table.Columns.Contains("标准编码"))
            {
                table.Columns["标准编码"].ColumnName = "ISBN";
            }
            if (table.Columns.Contains("书名"))
            {
                table.Columns["书名"].ColumnName = "题名";
            }
            if (table.Columns.Contains("正题名"))
            {
                table.Columns["正题名"].ColumnName = "题名";
            }
            if (table.Columns.Contains("批次号"))
            {
                table.Columns["批次号"].ColumnName = "批次";
            }
            if (table.Rows.Count > 0)
            {
                DataView dv = table.DefaultView;
                this.toolStripButton1.Enabled = true;
                if (table.Columns.Contains("ISBN"))
                {
                    //wait.label4.Text = "EXCEL去重中.. ";
                    //Application.DoEvents();
                    //table = dv.ToTable(true, new string[] { "ISBN","题名" });
                    int m = 0;
                    for (int i = table.Rows.Count-1; i >-1; i--)
                    {
                        m++;
                        string isbn = table.Rows[i]["ISBN"].ToString().Replace("-","");
                        string sisbn = StringUtil.BuingISBNSept(StringUtil.BarCodeToISBN(isbn));
                        using (DbOperHandler _open = new Common().Doh())
                        {
                            if (!flag)
                            {
                                _open.Reset();
                                //_open.ConditionExpress = " (REPLACE(标准编码,'-','')='" + isbn + "' or EXISTS (SELECT 主键码 FROM 检索编码库 WHERE 馆藏书目库.主键码 = 检索编码库.主键码 AND 数据='" + sisbn + "')) AND EXISTS (SELECT 主键码 FROM 采购库 WHERE 馆藏书目库.主键码 = 采购库.主键码 AND 状态='C')";
                                _open.ConditionExpress = " EXISTS (SELECT 主键码 FROM 检索编码库 WHERE 馆藏书目库.主键码 = 检索编码库.主键码 AND 数据='" + sisbn + "') AND EXISTS (SELECT 主键码 FROM 采购库 WHERE 馆藏书目库.主键码 = 采购库.主键码 AND 状态='C')";
                                if (!_open.Exist("馆藏书目库"))
                                {
                                    table.Rows.RemoveAt(i);
                                    //i--;
                                }
                            }
                        }
                        wait.label4.Text = "检测是否为采购中.. " + m ; 
                        Application.DoEvents();                        
                    }
                }
                this.BookData = new BindingSource();
                this.BookData.DataSource = table;
                this.BookData.PositionChanged += new EventHandler(this.BookData_PositionChanged);
                this.bindingNavigator1.BindingSource = this.BookData;
                this.excelGridView.DataSource = this.BookData;
                this.excelGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);//宽度自适应
                //this.excelGridView.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;//宽度自适应
                //this.excelGridView.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;//宽度自适应
                if (BookData.Count > 0)
                    this.BookData_PositionChanged(sender, e);
                for (int k = 0; k < this.excelGridView.Columns.Count; k++)
                {
                    string column = this.excelGridView.Columns[k].HeaderText;
                    switch (column)
                    {
                        case "书号":
                            this.excelGridView.Columns["书号"].HeaderText = "ISBN";
                            break;
                        case "条码":
                            this.excelGridView.Columns["条码"].HeaderText = "ISBN";
                            break;
                        case "条码号":
                            this.excelGridView.Columns["条码号"].HeaderText = "ISBN";
                            break;
                        case "标准编码":
                            this.excelGridView.Columns["标准编码"].HeaderText = "ISBN";
                            break;
                        case "isbn":
                            this.excelGridView.Columns["isbn"].HeaderText = "ISBN";
                            break;
                        case "书名":
                            this.excelGridView.Columns["书名"].HeaderText = "题名";
                            break;
                        case "图书名称":
                            this.excelGridView.Columns["图书名称"].HeaderText = "题名";
                            break;
                        case "主题名":
                            this.excelGridView.Columns["主题名"].HeaderText = "题名";
                            break;
                        case "正题名":
                            this.excelGridView.Columns["正题名"].HeaderText = "题名";
                            break;
                    }
                }
                for (int i = 0; i < this.excelGridView.ColumnCount; i++)
                {
                    string ht = this.excelGridView.Columns[i].HeaderText;
                }
            }
            this.ebtBox.Items.Clear();
            for (int i = 0; i < this.excelGridView.ColumnCount; i++)
            {
                this.ebtBox.Items.Add(this.excelGridView.Columns[i].HeaderText);
            }
            if (this.ebtBox.Items.Count > 0)
                this.ebtBox.SelectedIndex = 0;
            wait.Close();
            string time1 = watch.Elapsed.ToString();
            MessageBox.Show("导入：" + table.Rows.Count + "条，耗时：" + time1);
        }

        private void BookData_PositionChanged(object sender, EventArgs e)
        {
            this.marcGridS1.MarcType = 1;
            this.marcGrid1.MarcType = 1;
            DataRowView current = this.BookData.Current as DataRowView;
            string isbn = current["ISBN"].ToString().Trim();
            if (!string.IsNullOrEmpty(isbn))
            {
                string sisbn = StringUtil.BuingISBNSept(StringUtil.BarCodeToISBN(isbn));
                
                using (DbOperHandler _open = new Common().Doh())
                {
                    if (_open.dbType.ToString().Equals("ORACLE") || _open.odbType.ToString().Equals("ORACLE"))
                        flag = true;
                    else
                        flag = false;
                    if (!flag)
                    {
                        string sql = "select c.*,s.书目库名,g.题名 from 馆藏书目库 g left join 采购库 c on g.主键码=c.主键码 left join 书目数据库定义 s on g.库键码=s.库键码 left join 检索编码库 j on g.主键码 = j.主键码 where (REPLACE(g.标准编码,'-','')='" + isbn + "' or j.数据='" + sisbn + "') AND c.状态='C'";
                        //Console.WriteLine(sql);
                        _open.Reset();
                        _open.SqlCmd = sql;
                        DataTable dcg = _open.GetDataTable();
                        this.CgData = new BindingSource();
                        this.CgData.DataSource = dcg;
                        this.CgData.PositionChanged += new EventHandler(this.CgData_PositionChanged);
                        this.cgGridView.DataSource = this.CgData;
                        this.cgGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);//宽度自适应
                        if (CgData.Count > 0)
                            this.CgData_PositionChanged(sender, e);
                        _open.Reset();
                        _open.ConditionExpress = "EXISTS (SELECT 主键码 FROM 检索编码库 WHERE 馆藏书目库.主键码 = 检索编码库.主键码 AND 数据='" + sisbn + "') AND EXISTS (SELECT 主键码 FROM 采购库 WHERE 馆藏书目库.主键码 = 采购库.主键码 AND 状态='C')";
                        //_open.ConditionExpress = " (REPLACE(标准编码,'-','')='" + isbn + "' or EXISTS (SELECT 主键码 FROM 检索编码库 WHERE 馆藏书目库.主键码 = 检索编码库.主键码 AND 数据='" + sisbn + "')) AND EXISTS (SELECT 主键码 FROM 采购库 WHERE 馆藏书目库.主键码 = 采购库.主键码 AND 状态='C')";
                        if (_open.Exist("馆藏书目库"))
                        {
                            marc = _open.GetField("馆藏书目库", "marc").ToString();
                            zjm = _open.GetField("馆藏书目库", "主键码").ToString();
                            kjm = _open.GetField("馆藏书目库", "库键码").ToString();
                        }
                        else
                        {
                            zjm = "";
                            marc = "";
                        }
                        _open.Reset();
                        _open.ConditionExpress = "标准编码1='" + sisbn + "' or 标准编码2='" + sisbn + "' or 标准编码1='" + sisbn + "'";
                        if (_open.Exist("标准书目源库")) {
                            bzmarc = _open.GetField("标准书目源库", "marc").ToString();
                        }
                        else {
                            bzmarc = "";
                        }  
                    }
                    if (!string.IsNullOrEmpty(bzmarc))
                    {
                        try
                        {
                            MarcConvert convert = new MarcConvert();
                            convert.ISOData = bzmarc;
                            if (bzmarc.Split('\x001e')[0].Length < 25)
                            {
                                convert.MarcText = bzmarc;
                                convert.SetISOStr();
                                convert.SetISOtoMarc();
                            }
                            else
                            {
                                convert.SetISOtoMarc();
                            }
                            this.marcGridS1.MarcText = convert.MarcText;
                            this.marcGridS1.SetGridView();
                        }
                        catch { }
                    }
                    else
                    {
                        this.marcGridS1.MarcText = "";
                        this.marcGridS1.SetGridView();
                    }
                    if (!string.IsNullOrEmpty(marc))
                    {
                        this.marcGrid1.kjm = kjm;
                        this.marcGrid1.key = zjm;
                        try
                        {
                            MarcConvert convert = new MarcConvert();
                            convert.ISOData = marc;
                            if (marc.Split('\x001e')[0].Length < 25)
                            {
                                convert.MarcText = marc;
                                convert.SetISOStr();
                                convert.SetISOtoMarc();
                            }
                            else
                            {
                                convert.SetISOtoMarc();
                            }
                            this.marcGrid1.MarcText = convert.MarcText;
                            this.marcGrid1.SetGridView();
                        }
                        catch { }
                    }
                    else
                    {
                        this.marcGrid1.MarcText = "";
                        this.marcGrid1.SetGridView();
                    }
                }
            }
        }
        private void CgData_PositionChanged(object sender, EventArgs e)
        {
            DataRowView current = this.CgData.Current as DataRowView;
        }

        private void openBtn_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Title = "iso2709文件";
            dialog.Filter = "*.ISO|*.*";
            //dialog.ShowDialog();
            dialog.RestoreDirectory = true;//记录上次打开的目录
            dialog.Multiselect = false; //不允许选中多个文件
            dialog.ValidateNames = true;     //文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名
            dialog.CheckFileExists = true;  //验证路径有效性
            dialog.CheckPathExists = true; //验证文件有效性
            string strName = this.txtPath.Text.Trim();
            if (string.IsNullOrEmpty(strName))
            {
                if (dialog.ShowDialog() == DialogResult.OK)
                {
                    strName = dialog.FileName;
                    this.txtPath.Text = strName;
                }
            }
            else
            {
                if (MessageBox.Show("确认要清除当前文件，重新加载吗？", "询问", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
                {
                    strName = "";
                    this.txtPath.Text = "";
                    if (dialog.ShowDialog() == DialogResult.OK)
                    {
                        strName = dialog.FileName;
                        this.txtPath.Text = strName;
                    }
                }
            }
            if (strName == "")
            {
                MessageBox.Show("没有选择ISO文件！无法进行MARC数据上装");
                return;
            }
        }

        private void tcgBtn_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("将标准源MARC覆盖采购MARC，是否继续？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
            {
                if (this.excelGridView.Rows.Count > 0)
                {
                    using (DbOperHandler _open = new Common().Doh())
                    {
                        MarcConvert convert = new MarcConvert();
                        FrmWait wait = new FrmWait();
                        wait.Show();
                        for (int i = 0; i < this.excelGridView.Rows.Count; i++)
                        {
                            wait.label4.Text = "正在替换数据 " + (i + 1).ToString();
                            Application.DoEvents();
                            this.excelGridView.CurrentCell = this.excelGridView.Rows[i].Cells[0];
                            //模拟点击事件（点击选中行）
                            this.excel_CellClick(excelGridView, new DataGridViewCellEventArgs(excelGridView.SelectedCells[0].ColumnIndex, excelGridView.SelectedCells[0].RowIndex));
                            if (!string.IsNullOrEmpty(bzmarc))
                            {
                                if (!string.IsNullOrEmpty(marc))
                                {
                                    Console.WriteLine(marc);
                                    convert.MarcText = marc;
                                    string f905b = convert.GetASubField("905", "b", 1);
                                    if (string.IsNullOrEmpty(f905b))
                                    {
                                        _open.Reset();
                                        _open.ConditionExpress = "主键码=" + zjm;
                                        _open.AddFieldItem("marc", bzmarc);
                                        _open.Update("馆藏书目库");
                                        SaveMarc.SaveForZjm(Convert.ToInt32(zjm), Convert.ToInt32(kjm));
                                    }
                                }
                            }
                        }
                        wait.Close();
                    }
                }
            }
        }

        private void excel_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex != -1)
                if (e != null)
                    this.excelGridView.Rows[e.RowIndex].Selected = true;
        }

        private void marcBtn_Click(object sender, EventArgs e)
        {
            if (this.txtPath.Text.Equals(""))
            {
                MessageBox.Show("请选择ISO文件");
                this.openBtn_Click(sender, e);
            }
            else
            {
                int index = this.txtPath.Text.LastIndexOf("\\");
                string filename = this.txtPath.Text.Substring(index + 1);  //截取文件名
                if (MessageBox.Show("确认要上装【" + this.txtPath.Text + "】至标准源书目库【"+ this.skKay.Text+"】吗？", "询问", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
                {
                    Stopwatch watch = new Stopwatch();
                    watch.Start();
                    FrmWait wait = new FrmWait();
                    wait.Show();
                    StreamReader reader = new StreamReader(this.txtPath.Text, Encoding.Default);
                    string str;int m = 0,n=0,i=0;
                    while (reader.Peek() >= 0)
                    {
                        str = reader.ReadLine().ToString();
                        //Console.WriteLine("marc:"+str);
                        using (DbOperHandler _open = new Common().Doh())
                        {
                            if (str.Length > 0)
                            {
                                m += 1;
                                wait.label4.Text = "正在导入MARC数据 " + m.ToString();
                                Application.DoEvents();
                                Books b = GetBooks.SetBooks(str);
                                if (b != null)
                                {
                                    b.Kjm = _bzkjm;
                                    object[,] value = new object[11, 2] {
                                        {"库键码",b.Kjm},
                                        {"题名",b.Title},
                                        {"题名缩写",b.Tmsx},
                                        {"责任者",b.Author},
                                        {"出版者",b.Press},
                                        {"出版日期",b.Pdate},
                                        {"标准编码1",b.Sisbn},
                                        {"处理日期",b.Clrq},
                                        {"索书号",b.Ssh},
                                        {"操作员",this.uid},
                                        {"marc",b.Marc}
                                    };
                                    _open.Reset();
                                    _open.AddFieldItems(value);
                                    _open.Insert("标准书目源库");
                                    n += 1;
                                }
                                else
                                    i += 1;
                            }
                        }
                    }
                    wait.Close();
                    var mSeconds = watch.ElapsedMilliseconds / 1000;
                    if (m > 0)
                    {
                        MessageBox.Show("文件【" + filename + "】[" + n + "]条成功导入;耗时:" + mSeconds.ToString() + " 秒!");
                    }
                    else
                    {
                        MessageBox.Show("MARC文件有误!");
                    }
                }
            }
        }

        private void skKay_SelectedIndexChanaged(object sender, EventArgs e)
        {
            int index = this.skKay.SelectedIndex;
            this._bzkjm = this.DbKayList[index];
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            
        }
    }
}
